using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public PlnctaVO getPlncta(int? idPlnCta, string plnCta)
		{
			PlnctaVO plnctaVO = null;
			List<PlnctaVO> listPlnctaVOs = getPlnctas(idPlnCta, plnCta, null, null, null, null);
			if (listPlnctaVOs != null && listPlnctaVOs.Count > 0)
			{
				plnctaVO = listPlnctaVOs[0];
			}
			return plnctaVO;
		}

		public PlnctaVO getPlncta(int? idPlnCta)
		{
			return getPlncta(idPlnCta, null);
		}

		public List<PlnctaVO> getPlnctas()
		{
			return getPlnctas(null, null, null, null, null, null);
		}

		public List<PlnctaVO> getPlnctas(int? idPlnCta)
		{
			return getPlnctas(idPlnCta, null, null, null, null, null);
		}

		public List<PlnctaVO> getPlnctas(int? idPlnCta, string plnCta)
		{
			return getPlnctas(idPlnCta, plnCta, null, null, null, null);
		}

		public List<PlnctaVO> getPlnctas(int? idPlnCta, string plnCta, int? idFilial, int? idPlnCtaPai, bool? sintetico, char? natureza)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT plc.idPlnCta as plc_idPlnCta, plc.plnCta as plc_plnCta, plc.sintetico as plc_sintetico, plc.natureza as plc_natureza, plc.idFilial as plc_idFilial, fil.filial as fil_filial, plc.idPlnCtaPai as plc_idPlnCtaPai, plc2.plnCta as plc2_plnCta, plc.cadastro as plc_cadastro, plc.cadastradoPor as plc_cadastradoPor, plc.edicao as plc_edicao, plc.editadoPor as plc_editadoPor  ");
			query.AppendLine("FROM plncta as plc LEFT OUTER JOIN filial as fil on plc.idFilial=fil.idFilial  LEFT OUTER JOIN plncta as plc2 on plc.idPlnCtaPai=plc2.idPlnCta  ");
			if (idPlnCta != null)
			{
				query.AppendLine(where + "plc.idPlnCta=@idPlnCta ");
				where = "AND ";
			}
			if (plnCta != null)
			{
				query.AppendLine(where + "plc.plnCta LIKE @plnCta ");
				where = "AND ";
			}
			if (sintetico != null)
			{
				query.AppendLine(where + "plc.sintetico=@sintetico ");
				where = "AND ";
			}
			if (natureza != null)
			{
				query.AppendLine(where + "plc.natureza=@natureza ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "plc.idFilial=@idFilial ");
				where = "AND ";
			}
			if (idPlnCtaPai != null)
			{
				query.AppendLine(where + "plc.idPlnCtaPai=@idPlnCtaPai ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY plc.plnCta ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idPlnCta!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", idPlnCta));
			if (plnCta!= null) cmd.Parameters.Add(getParameter(cmd, "@plnCta", "%"+plnCta+"%"));
			if (sintetico!= null) cmd.Parameters.Add(getParameter(cmd, "@sintetico", sintetico.Value?"S":"N"));
			if (natureza!= null) cmd.Parameters.Add(getParameter(cmd, "@natureza", natureza));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (idPlnCtaPai!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlnCtaPai", idPlnCtaPai));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<PlnctaVO> listPlncta = new List<PlnctaVO>();

				while (reader.Read())
				{
					PlnctaVO plnctaVO = PlnctaVO.CreateVO();
					plnctaVO.idPlnCta = reader.GetInt32(reader.GetOrdinal("plc_idPlnCta"));
					plnctaVO.plnCta = reader.GetString(reader.GetOrdinal("plc_plnCta"));
					plnctaVO.sintetico = reader.GetString(reader.GetOrdinal("plc_sintetico")).Equals("S");
					plnctaVO.natureza = reader.GetChar(reader.GetOrdinal("plc_natureza"));
					plnctaVO.idFilial = reader.GetInt32(reader.GetOrdinal("plc_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						plnctaVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					if (!reader.IsDBNull(reader.GetOrdinal("plc_idPlnCtaPai")))
						plnctaVO.idPlnCtaPai = reader.GetInt32(reader.GetOrdinal("plc_idPlnCtaPai"));
					if (!reader.IsDBNull(reader.GetOrdinal("plc2_plnCta")))
						plnctaVO.idPlnCtaPaiVO.plnCta = reader.GetString(reader.GetOrdinal("plc2_plnCta"));
					plnctaVO.cadastro = reader.GetDateTime(reader.GetOrdinal("plc_cadastro"));
					plnctaVO.cadastradoPor = reader.GetString(reader.GetOrdinal("plc_cadastradoPor"));
					plnctaVO.edicao = reader.GetDateTime(reader.GetOrdinal("plc_edicao"));
					plnctaVO.editadoPor = reader.GetString(reader.GetOrdinal("plc_editadoPor"));


					listPlncta.Add(plnctaVO);
				}
				reader.Close();
				return listPlncta;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setPlncta(PlnctaVO plnctaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE plncta ");
			query.AppendLine("SET plnCta=@plnCta, sintetico=@sintetico, natureza=@natureza, idFilial=@idFilial, idPlnCtaPai=@idPlnCtaPai,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idPlnCta=@idPlnCta ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", plnctaVO.idPlnCta));
			cmd.Parameters.Add(getParameter(cmd, "@plnCta", plnctaVO.plnCta));
			cmd.Parameters.Add(getParameter(cmd, "@sintetico", (plnctaVO.sintetico==null?" ":(plnctaVO.sintetico.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@natureza", plnctaVO.natureza));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", plnctaVO.idFilial));
			if (plnctaVO.idPlnCtaPai == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCtaPai", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCtaPai", plnctaVO.idPlnCtaPai));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", plnctaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", plnctaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", plnctaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", plnctaVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newPlncta(PlnctaVO plnctaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO plncta ");
			query.AppendLine("  ( plnCta, sintetico, natureza, idFilial, idPlnCtaPai, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @plnCta, @sintetico, @natureza, @idFilial, @idPlnCtaPai, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@plnCta", plnctaVO.plnCta));
			cmd.Parameters.Add(getParameter(cmd, "@sintetico", (plnctaVO.sintetico==null?" ":(plnctaVO.sintetico.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@natureza", plnctaVO.natureza));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", plnctaVO.idFilial));
			if (plnctaVO.idPlnCtaPai == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCtaPai", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPlnCtaPai", plnctaVO.idPlnCtaPai));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", plnctaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", plnctaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", plnctaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", plnctaVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('plncta') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delPlncta(PlnctaVO plnctaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM plncta ");
			query.AppendLine("WHERE idPlnCta=@idPlnCta ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idPlnCta", plnctaVO.idPlnCta));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountPlnctaxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM plncta WHERE IdFilial=@IdFilial ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountPlnctaxIdPlnCtaPai(int? idPlnCtaPai)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idPlnCtaPai) as ocorrencias FROM plncta WHERE IdPlnCtaPai=@IdPlnCtaPai ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdPlnCtaPai", idPlnCtaPai));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
